package com.shengruan.admin.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.shengruan.admin.vo.UserAccount;
import com.shengruan.admin.vo.UserRecharge;
import com.shengruan.framework.jdbc.BaseJdbc;
import com.shengruan.framework.jdbc.BeanHandler;
import com.shengruan.framework.jdbc.DBUtils;

public class UserAccountJdbc extends BaseJdbc {

    Logger logger = LoggerFactory.getLogger(getClass());

    public static final String USERACCOUNT_INSERT = "INSERT INTO user_account (userId, amount, rechargeAmount, withdrawAmount, BiddingCount)VALUES(?, ?, ?, ?, ?)";
    public static final String USERACCOUNT_GETBY_USERID = "select * from user_account ua where ua.userId = ?";
    public static final String USERACCOUNT_UPDATE = "UPDATE user_account SET userId = ? , amount = ? , rechargeAmount = ? , withdrawAmount = ? , BiddingCount = ? , creationTime = ? WHERE id = ?";
    public static final String BIDHISTORY_INSERT = "INSERT INTO bidhistory (TYPE, biddingCount, userId, creationTime)VALUES(?, ?, ?, ?)";


    /**
     * 用户赠送标
     *
     * @param bidding
     */
    public void presentationBidding(int userId, int bidding, int type) {
        UserAccount userAccount = getByUserId(userId);

        Connection con = this.getConnection();
        PreparedStatement psta = null;
        PreparedStatement bidhistoryPsta = null;
        try {

            //con.setAutoCommit(false);//设置事务为手动控制
            psta = con.prepareStatement("UPDATE user_account SET BiddingCount = ? WHERE id = ?");
            psta.setInt(1, userAccount.getBiddingCount() + bidding);
            psta.setInt(2, userAccount.getId());
            psta.executeUpdate();


            //记录用户赠标记录
            bidhistoryPsta = con.prepareStatement(BIDHISTORY_INSERT);
            bidhistoryPsta.setInt(1, type);
            bidhistoryPsta.setInt(2, bidding);
            bidhistoryPsta.setInt(3, userId);
            bidhistoryPsta.setLong(4, System.currentTimeMillis());
            bidhistoryPsta.executeUpdate();

            //con.commit();//提交事务
            //con.setAutoCommit(true);
        } catch (SQLException e) {
            /*try {
                con.rollback();//回滚事务
            } catch (SQLException ex) {
                ex.printStackTrace();
            }*/
            e.printStackTrace();
        } finally {
            DBUtils.close(bidhistoryPsta);
            DBUtils.close(psta, con);
        }
    }


    public void alipayment(UserRecharge userRecharge) {
        UserAccount userAccount = getByUserId(userRecharge.getUserId());

        Connection con = this.getConnection();
        PreparedStatement psta = null;
        PreparedStatement userRechargePsta = null;
        try {

            //con.setAutoCommit(false);//设置事务为手动控制
            psta = con.prepareStatement("UPDATE user_account SET BiddingCount = ?,rechargeAmount = ?  WHERE id = ?");
            int bidding = userAccount.getBiddingCount() + userRecharge.getBidding() + userRecharge.getGiftBidding();
            psta.setInt(1, bidding);
            double rechargeAmount = userAccount.getRechargeAmount() + userRecharge.getAmount();
            psta.setDouble(2, rechargeAmount);
            psta.setInt(3, userAccount.getId());
            psta.executeUpdate();


            //记录用户赠标记录
            userRechargePsta = con.prepareStatement("UPDATE user_recharge SET transTime = ? , STATUS = ? , tradeNo = ? , orderSubject = ? , orderBody = ? , buyerEmail = ? WHERE id = ?");
            userRechargePsta.setLong(1, userRecharge.getTransTime());
            userRechargePsta.setInt(2, userRecharge.getStatus());
            userRechargePsta.setString(3, userRecharge.getTradeNo());
            userRechargePsta.setString(4, userRecharge.getOrderSubject());
            userRechargePsta.setString(5, userRecharge.getOrderBody());
            userRechargePsta.setString(6, userRecharge.getBuyerEmail());
            userRechargePsta.setInt(7, userRecharge.getId());
            userRechargePsta.executeUpdate();

            //con.commit();//提交事务
            //con.setAutoCommit(true);
        } catch (SQLException e) {
            /*try {
                con.rollback();//回滚事务
            } catch (SQLException ex) {
                ex.printStackTrace();
            }*/
            e.printStackTrace();
        } finally {
            DBUtils.close(userRechargePsta);
            DBUtils.close(psta, con);
        }
    }

    public void save(UserAccount userAccount) {
        Connection conn = this.getConnection();
        Object[] pram = {userAccount.getUserId(), userAccount.getAmount(), userAccount.getRechargeAmount(), userAccount.getWithdrawAmount(), userAccount.getBiddingCount()};
        try {
            this.execute(conn, USERACCOUNT_INSERT, pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }


    /**
     * 更新用户账户信息
     *
     * @param userAccount
     */
    public void update(UserAccount userAccount) {
        Connection conn = this.getConnection();

        Object[] pram = {userAccount.getUserId(), userAccount.getAmount(), userAccount.getRechargeAmount(), userAccount.getWithdrawAmount(), userAccount.getBiddingCount(), userAccount.getCreationTime(), userAccount.getId()};

        try {
            this.execute(conn, USERACCOUNT_UPDATE, pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }


    /**
     * 根据用户Id得到用户账户信息
     *
     * @param userId
     * @return
     */
    public UserAccount getByUserId(int userId) {
        Connection conn = this.getConnection();
        UserAccount userAccount = null;
        try {
            userAccount = this.query(conn, USERACCOUNT_GETBY_USERID, new BeanHandler<UserAccount>(UserAccount.class), userId);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return userAccount;
    }
    
    /***
     * 获得用户投标数
     * @param userId
     * @return
     */
    public Integer getBiddingCount(int userId)
    {
    	String sql = "select * from user_account ua where ua.userId = ?";
    	 Connection conn = this.getConnection();
         UserAccount userAccount = null;
         try {
             userAccount = this.query(conn, sql, new BeanHandler<UserAccount>(UserAccount.class), userId);
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             this.close(conn);
         }
         return userAccount.getBiddingCount();
    }
    
    /**
     * 更新用户账户信息
     *
     * @param userId
     */
    public void updateBiddingCount(int userId) {
        Connection conn = this.getConnection();

        String sql = "update user_account set biddingCount = biddingCount-1 where userId=?";
        try {
            this.execute(conn, sql, userId);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }
}
